﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: May 19 2016
-- Description:	
-- =============================================
CREATE FUNCTION [dbo].[DataConversion_ParentInfo] 
(

)
RETURNS 
@JSONtoWrite TABLE (JSONvalue nvarchar(MAX),EntityID int,JSONvalue2 nvarchar(MAX),WriteType nvarchar(100))
AS
BEGIN

	--PRINT 'Insert new parents/Staff into the database.'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"EntityID":"' + CAST(COALESCE(P.EntityID,'-1') AS NVARCHAR)
					  + '","PersonID":"' + CAST(OP.ParentID+10000 AS NVARCHAR)
					  + COALESCE('","FirstName":"' + OP.FirstName,'')
					  + COALESCE('","MiddleName":"' + OP.MiddleName,'')
					  + COALESCE('","LastName":"' + OP.LastName,'')
					  + COALESCE('","PreferredName":"' + OP.PreferedName,'')
					  + '","Gender":"' + (CASE WHEN OP.Sex = 0 THEN 'Female' ELSE 'Male' END)
					  + '","EntityType":"' + 'Person' + '"' +
			CASE WHEN NOT RS.EntityID IS NULL THEN ',"ShortRelationshipName":"Parent"'
												 + ',"Entity2ID":"' + CAST(RS.EntityID AS NVARCHAR) + '"}' 
				 -- If they did not have children at RVA they are a staff member.  This 
				 -- system only supports current staff. 
				 ELSE ',"ShortRelationshipName":"Staff"' 
					+ ',"Entity2ID":"10000"' + ',"StartDate":"'+CAST(CYT.StartDate AS NVARCHAR)+'"' + ',"EndDate":"'+CAST(CYT.EndDate AS NVARCHAR)+'"}' 
			END	AS JSONvalue,-1 AS EntityID,'Entity' As WriteType
	--SELECT * 
	FROM 
	OPENQUERY(STUDDATA97,'SELECT PR.*,SL.Login FROM Parents AS PR 
					LEFT JOIN [Staff Logins] AS SL ON PR.ParentID = SL.ParentID') OP
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Current_YearTerm}''') AS CYT
					ON 2>1
	LEFT JOIN 	OPENQUERY(STUDDATA97,'SELECT MIN(S.StudentID) AS SID1,MotherID,FatherID FROM Students AS S INNER JOIN [Enrollment-Term] AS ET ON ET.StudentID = S.StudentID
					WHERE [Type of Term] = ''Term'' AND Term > 0  GRoup By MotherID,FatherID') S
			ON OP.ParentID = S.MotherID OR OP.ParentID = S.FatherID
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS RS
					ON RS.PersonID = CAST(S.SID1 AS NVARCHAR)
	LEFT JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS P
					ON P.PersonID = CAST(OP.ParentID+10000 AS NVARCHAR)
					-- Limits this query to people who are not in the system and are either current staff or parents of students
	WHERE P.EntityID IS NULL AND (NOT RS.EntityID IS NULL OR (OP.Staff = 1 AND OP.[Staff Active Now?] = 1 ))

	--PRINT 'Update Parent and Staff info'
	INSERT INTO @JSONtoWrite(JSONvalue,EntityID,WriteType)
	SELECT '{"EntityID":"' + CAST(COALESCE(P.EntityID,'-1') AS NVARCHAR)
					  + '","PersonID":"' + CAST(OP.ParentID+10000 AS NVARCHAR)
					  + COALESCE('","FirstName":"' + OP.FirstName,'')
					  + COALESCE('","MiddleName":"' + OP.MiddleName,'')
					  + COALESCE('","LastName":"' + OP.LastName,'')
					  + COALESCE('","PreferredName":"' + OP.PreferedName,'')
					  + COALESCE('","Logon":"' + OP.Login,'')
					  + '","Gender":"' + (CASE WHEN OP.Sex = 0 THEN 'Female' ELSE 'Male' END)
					  + '","EntityType":"' + 'Person' + '"}' AS JSONvalue,P.EntityID AS EntityID,'Entity' As WriteType
	--SELECT * 
	FROM 
	OPENQUERY(STUDDATA97,'SELECT PR.*,SL.Login FROM Parents AS PR 
					LEFT JOIN [Staff Logins] AS SL ON PR.ParentID = SL.ParentID') OP
	INNER JOIN OPENQUERY(MBOA,'SET FMTONLY OFF; EXEC RafikiDB.dbo.ExecuteRafikiNOSQL @NOSQL = ''SELECT * FROM {Person}''') AS P
					ON P.PersonID = CAST(OP.ParentID+10000 AS NVARCHAR)
	WHERE (
		((P.FirstName IS NULL AND NOT OP.FirstName IS NULL) OR P.FirstName <> OP.FirstName) OR
		((P.MiddleName IS NULL AND NOT OP.MiddleName IS NULL) OR P.MiddleName <> OP.MiddleName) OR
		((P.LastName IS NULL AND NOT OP.LastName IS NULL) OR P.LastName <> OP.LastName) OR
		((P.PreferredName IS NULL AND NOT OP.PreferedName IS NULL) OR P.PreferredName <> OP.PreferedName) OR
		((P.Gender IS NULL AND NOT OP.Sex IS NULL) OR NOT P.Gender LIKE (CASE WHEN OP.Sex = 0 THEN 'Female' ELSE 'Male' END)) OR
		((P.Logon IS NULL AND NOT OP.Login IS NULL) OR P.Logon <> OP.Login)
	)

	RETURN 
END